package com.web.hotel.dao;

import com.util.LogUtil;
import com.web.hotel.po.City;
import com.web.hotel.po.Region;
import com.web.hotel.po.mapper.CityMapper;
import com.web.hotel.po.mapper.StringMapper;
import com.web.hotel.service.CityService;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service(value = "cityService")
@Transactional(rollbackFor = {Exception.class})
public class CityDAO implements CityService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Resource
    SessionFactory factory;


    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public List<City> getCityByName(String cityName) {
        List<City> cityList = new ArrayList<City>();
        Session s = null;
        StringBuffer querySQL = new StringBuffer();
        StringBuffer whereSQL = new StringBuffer();
        if (cityName != null && !cityName.equals("")) {
            whereSQL.append(" and cityName like '%" + cityName + "%'");
        }
        querySQL.append("from City c");
        querySQL.append(" where 1 = 1");
        if (whereSQL.length() > 0) {
            querySQL.append(whereSQL);
        }
        querySQL.append(" order by cityName asc");
        try {
            s = factory.getCurrentSession();
            Query query = s.createQuery(querySQL.toString());
            query.setFirstResult(0);
            query.setMaxResults(10);
            cityList = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cityList;
    }


    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public List<Region> getAllCityByName(String cityName) {
        List<Region> regionList = new ArrayList<Region>();
        Session s = null;
        StringBuffer querySQL = new StringBuffer();
        StringBuffer whereSQL = new StringBuffer();
        if (cityName != null && !cityName.equals("")) {
            if (isContainChinese(cityName)) {
                whereSQL.append(" and name like '%" + cityName + "%'");
            } else {
                whereSQL.append(" and short = '" + cityName + "'");
            }

        }
        querySQL.append("from Region r");
        querySQL.append(" where 1 = 1");
        if (whereSQL.length() > 0) {
            querySQL.append(whereSQL);
        }
        querySQL.append(" order by name asc");
        try {
            s = factory.getCurrentSession();
            Query query = s.createQuery(querySQL.toString());
            query.setFirstResult(0);
            query.setMaxResults(9);
            regionList = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return regionList;
    }

    public List<String> getLandmarks(String cityName) {
        String sql =
                "SELECT\n" +
                        "	DISTINCT(landmarks)\n" +
                        "FROM\n" +
                        "	t_city\n" +
                        "WHERE\n" +
                        "	cityName = ?";
        List<String> param = new ArrayList<String>();
        param.add(cityName);
        System.out.println(sql);
        return jdbcTemplate.query(sql, param.toArray(), new StringMapper());
    }

    //正则表达式判断是否包含中文
    public static boolean isContainChinese(String str) {
        Pattern p = Pattern.compile("[\u4e00-\u9fa5]");
        Matcher m = p.matcher(str);
        if (m.find()) {
            return true;
        }
        return false;
    }


    public Map<String, Object> listGrid(HttpServletRequest request) {
        List<City> cityList = new ArrayList<City>();
        Map<String, Object> dataMap = new HashMap<String, Object>();
        Session s = null;
        /**
         * 接受客户端传递的排序参数
         *
         */
        String sortOrder = request.getParameter("order");
        String sortField = request.getParameter("sort");

        /**
         * 接受客户端传递的分页参数
         *
         */
        String currentPage = request.getParameter("page");
        String pageSize = request.getParameter("rows");
        if (currentPage == null || currentPage.equals("")) {
            currentPage = "1";
        }
        if (pageSize == null || pageSize.equals("")) {
            pageSize = "10";
        }
        int int_currentPage = Integer.parseInt(currentPage);
        int int_pageSize = Integer.parseInt(pageSize);

        int startRow = (int_currentPage - 1) * int_pageSize;
        Long rsCount = new Long(0);


        /**
         * 接收客户端的查询参数
         *
         */

        String param_cityName = request.getParameter("param_cityName");
        String param_areaName = request.getParameter("param_areaName");
        String param_createUser = request.getParameter("param_createUser");

        StringBuffer querySQL = new StringBuffer();
        StringBuffer countSQL = new StringBuffer();
        StringBuffer whereSQL = new StringBuffer();

        if (param_cityName != null && !param_cityName.equals("")) {
            whereSQL.append(" and cityName like '%" + param_cityName + "%'");
        }
        if (param_areaName != null && !param_areaName.equals("")) {
            whereSQL.append(" and areaName like '%" + param_areaName + "%'");
        }
        if (param_createUser != null && !param_createUser.equals("")) {
            whereSQL.append(" and createUser like '%" + param_createUser + "%'");
        }
        /**
         * 1.先取出总记录数
         */
        countSQL.append("select count(*) from City");
        countSQL.append(" where 1 = 1");
        if (whereSQL.length() > 0) {
            countSQL.append(whereSQL);
        }

        /**
         * 2.产生分页语句
         */
        querySQL.append("from City r");
        querySQL.append(" where 1 = 1");
        if (whereSQL.length() > 0) {
            querySQL.append(whereSQL);
        }

        if (sortField != null && !sortField.equals("")) {
            querySQL.append(" order by " + sortField + " " + sortOrder + "");
        } else {
            querySQL.append(" order by cityId asc");
        }


        //querySQL.append(" limit "+startRow+" , "+int_pageSize+"");

        try {
            s = factory.getCurrentSession();
            rsCount = (Long) (s.createQuery(countSQL.toString()).uniqueResult());
            Query query = s.createQuery(querySQL.toString());
            query.setFirstResult(startRow);
            query.setMaxResults(int_pageSize);
            cityList = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }

        dataMap.put("total", rsCount);
        dataMap.put("rows", cityList);

        return dataMap;
    }

    public List<City> getCityByLandMarks(String cityName, String landmarks) {
        String sql =
                "SELECT\n" +
                        "	*\n" +
                        "FROM\n" +
                        "	t_city\n" +
                        "WHERE\n" +
                        "	cityName = ?\n" +
                        "AND landmarks = ?";
        LogUtil.sqlLog("根据城市名称与主要地标获得城市ID",sql);
        List<String> params = new ArrayList<String>();
        params.add(cityName);
        params.add(landmarks);
        return jdbcTemplate.query(sql,params.toArray(),new CityMapper());
    }

    public Map<String, Object> deleteCityById(String cityIds) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        Session s = null;
        StringBuffer deleteSQL = new StringBuffer();
        deleteSQL.append("delete ");
        deleteSQL.append(" from t_city where cityId in");
        deleteSQL.append("(" + cityIds + ")");

        try {
            s = factory.getCurrentSession();
            int count = s.createSQLQuery(deleteSQL.toString()).executeUpdate();
            dataMap.put("flag", true);
            dataMap.put("msg", "成功删除 " + count + "条数据");
        } catch (Exception e) {
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }
        return dataMap;
    }


    public Map<String, Object> editCity(City city) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        if (city == null) {
            dataMap.put("flag", false);
            dataMap.put("msg", "服务端未接收到客户端传递的数据");
            return dataMap;
        }
        try {
            factory.getCurrentSession().update(city);
            dataMap.put("flag", true);
            dataMap.put("msg", "修改成功 ，确认保存？");
        } catch (Exception e) {
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }
        return dataMap;
    }


    public Map<String, Object> addCity(City city) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        if (city == null) {
            dataMap.put("flag", false);
            dataMap.put("msg", "服务端未接收到客户端传递的数据");
            return dataMap;
        }
        try {
            factory.getCurrentSession().save(city);
            dataMap.put("flag", true);
            dataMap.put("msg", "保存城市成功,继续添加？");
        } catch (Exception e) {
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }
        return dataMap;
    }

    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public City getCityById(int cityId) {
        return (City) factory.getCurrentSession().get(City.class, cityId);
    }


    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public City getCityByAreaName(String areaName) {
        Session s = null;
        StringBuffer queryHQL = new StringBuffer();
        queryHQL.append("from City");
        queryHQL.append(" where areaName = '" + areaName + "'");
        try {
            List<City> cityList = factory.getCurrentSession().createQuery(queryHQL.toString()).list();
            if (cityList != null && cityList.size() > 0) {
                return cityList.get(0);
            } else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


}
